﻿using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;

namespace Entersoft.Data.DotNetDAL.SQL
{
    public class Persistence : IDisposable
    {
        private readonly SqlConnection mConnection;

        public SqlConnection Connection
        {
            get { return mConnection; }
        }

        private SqlTransaction mTransaction;
        private bool mError;

        public string DescError;
        //private string motor;
        //private object[] ParamNombre;
        //private object[] ParamTipo;
        //private object[] ParamValor;

        private readonly bool mCommited;

        public string mNegTransaccion;
        public string mStoreProcedure;

        List<object> mParametro;

        private List<string> mErrores;

        /// <summary>
        /// 
        /// </summary>
        public List<string> Errores
        {
            get { return mErrores ?? (mErrores = new List<string>()); }
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="Valor"></param>
        public void AddParametro(object Valor)
        {
            if (mParametro == null)
                mParametro = new List<object>();

            mParametro.Add(Valor);
        }

        /// <summary>
        /// 
        /// </summary>
        public void Conectar()
        {
            try
            {
                mConnection.Open();
                mTransaction = mConnection.BeginTransaction();
            }
            catch (Exception ex)
            {
                mErrores.Add(ex.Message);
                mError = true;
            }
        }

        /// <summary>
        /// 
        /// </summary>
        /// <returns></returns>
        public void ConectarReadUncommitted()
        {
            try
            {
                mConnection.Open();
                mTransaction = mConnection.BeginTransaction(IsolationLevel.ReadUncommitted);
            }
            catch (Exception ex)
            {
                if (mErrores == null)
                    mErrores = new List<string>();

                mErrores.Add(ex.Message);
                mError = true;
            }
        }

        /// <summary>
        /// 
        /// </summary>
        public void ConectarReadcommitted()
        {
            try
            {
                mConnection.Open();
                mTransaction = mConnection.BeginTransaction();
            }
            catch (Exception ex)
            {
                mErrores.Add(ex.Message);
                mError = true;
            }
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="Commited"></param>
        /// <param name="ConfigCnn"></param>
        public Persistence(bool Commited, string ConfigCnn)
        {
            mConnection = new SqlConnection();

            //ConfigCnn = ConfigurationManager.ConnectionStrings[ConfigCnn].ConnectionString.Trim();
    
            if (mConnection != null)
                if (mConnection.State != ConnectionState.Closed)
                    return;

            mConnection.ConnectionString = ConfigCnn;
            mCommited = Commited;

            if (Commited) 
                ConectarReadcommitted();
            else 
                ConectarReadUncommitted();
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="rp"></param>
        /// <param name="Dg_NroRegistros"></param>
        /// <param name="NroPagina"></param>
        /// <returns></returns>
        public object ExecuteDataTable(ref DotNetResponse.SQL.Persistence rp, int Dg_NroRegistros, int NroPagina)
        {
            DataTable retorno = null;

            try
            {
                if (rp == null)
                    throw new ArgumentNullException("rp");

                retorno = new DataTable {TableName = "nodos"};

                var dr = ClientHelper.ExecuteReader(mTransaction, rp.StoredProcedure);

                var y = Dg_NroRegistros * NroPagina;
                var x = (y - Dg_NroRegistros) + 1;

                for (var i = 0; i <= dr.FieldCount - 1; i++)
                    retorno.Columns.Add(dr.GetName(i));

                var ct = 1;

                while (dr.Read())
                {
                    if (ct >= x & ct <= y)
                    {
                        //Lleno mi datatable
                        var row = retorno.NewRow();

                        for (var i = 0; i <= dr.FieldCount - 1; i++)
                            row[i] = dr.GetValue(i);

                        rp.TieneDatos = true;
                        retorno.Rows.Add(row);
                    }

                    ct = ct + 1;
                }

                rp.DataTable = retorno;

            }
            catch (Exception ex)
            {
                mError = true;

                if (rp != null)
                {
                    rp.TieneDatos = false;
                    rp.AddError(ex.Message);
                }
            }

            return retorno;
        }

        //Public Function Executedataset(ByRef rp As DotNetResponse.SQL.Persistence, ByVal sentencia As String, ByRef Obj() As Object)
        //
        //    Dim dr
        //    Try
        //        Dim paginacion As Integer = Obj(Obj.Length - 1)
        //        Dim Pagina As Integer = Obj(Obj.Length - 2)
        //        Dim dt As New DataTable
        //        dt.TableName = "nodos"
        //
        //        Dim Row As DataRow
        //        Dim cell As New DataColumn
        //        If MyProvider = Provider.SQLOLEDB Then
        //            dr = OledbHelper.ExecuteReader(DirectCast(mTransaction, OleDbTransaction), CommandType.Text, sentencia)
        //        End If
        //        If MyProvider = Provider.SQLCLIENT Then
        //            dr = ClientHelper.ExecuteReader(DirectCast(mTransaction, SqlTransaction), CommandType.Text, sentencia)
        //        End If
        //
        //        Dim i As Integer
        //        For i = 0 To dr.FieldCount - 1
        //            dt.Columns.Add(dr.GetName(i))
        //        Next
        //        Dim ct As Integer = 1, pag As Integer = 0
        //        Do While dr.Read
        //            pag = pag + 1
        //            If ((paginacion * Pagina) - paginacion) + 1 <= pag And (paginacion * Pagina) >= pag Then
        //                Row = dt.NewRow
        //                For i = 0 To dr.FieldCount - 1
        //                    Row(i) = dr.GetValue(i)
        //                Next
        //                rp.TieneDatos = True
        //                dt.Rows.Add(Row)
        //            End If
        //        Loop
        //        rp.DataSet.Tables.Add(dt)
        //        dr.Close()

        //        If Not rp Is Nothing Then
        //            If rp.TieneErrores Then
        //                erro = True
        //            End If
        //        End If
        //    Catch ex As Exception
        //        erro = True
        //        Try
        //            rp.TieneDatos = False
        //            rp.TieneErrores = True
        //            rp.Add(ex.Message
        //            dr.Close()
        //        Catch exinterno As Exception
        //        End Try
        //    End Try
        //End Function

        /// <summary>
        /// 
        /// </summary>
        /// <param name="rp"></param>
        /// <param name="sentencia"></param>
        public void ExecuteNonQuery(ref DotNetResponse.SQL.Persistence rp, string sentencia)
        {
            try
            {
                if (rp == null)
                    throw new ArgumentNullException("rp");

                if (string.IsNullOrEmpty(sentencia))
                    throw new ArgumentNullException("sentencia");

                ClientHelper.ExecuteNonquery(ref rp, mTransaction, sentencia);

                if (rp.TieneErrores)
                    mError = true;

            } catch (Exception ex)
            {
                mError = true;

                if (rp != null)
                {
                    rp.TieneDatos = false;
                    rp.AddError(ex.Message);
                }
            }
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="rp"></param>
        public void ExecuteNonQuery(ref DotNetResponse.SQL.Persistence rp)
        {
            try
            {
                if (rp == null)
                    throw new ArgumentNullException("rp");

                var vParametro = rp.Parametro;

                ClientHelper.ExecuteNonQuery(mTransaction, rp.StoredProcedure, false, ref vParametro);

                rp.Parametro = vParametro;

                if (rp.TieneErrores)
                    mError = true;

            }
            catch (Exception ex)
            {
                mError = true;

                if (rp != null)
                {
                    rp.TieneDatos = false;
                    rp.AddError(ex.Message);
                }
            }
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="rp"></param>
        /// <param name="sentencia"></param>
        public void ExecuteScalar(ref DotNetResponse.SQL.Persistence rp, string sentencia)
        {
            try
            {
                if (rp == null)
                    throw new ArgumentNullException("rp");

                rp.GetScalar = ClientHelper.ExecuteScalar(ref rp, mTransaction, sentencia);

                if ((rp != null))
                {
                    rp.TieneDatos = rp.GetScalar != null;

                    if (rp.TieneErrores)
                        mError = true;
                }
            }
            catch (Exception ex)
            {
                mError = true;

                if (rp != null)
                {
                    rp.TieneDatos = false;
                    rp.AddError(ex.Message);
                }
            }
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="rp"></param>
        public void ExecuteScalar(ref DotNetResponse.SQL.Persistence rp)
        {
            try {
                if (rp == null)
                    throw new ArgumentNullException("rp");

                rp.GetScalar = ClientHelper.ExecuteScalar(mTransaction, rp.StoredProcedure, rp.Parametro);

                if (rp.GetScalar == null)
                    rp.TieneDatos = false;
                
                if (rp != null)
                    if (rp.TieneErrores)
                        mError = true;

            } catch (Exception ex) {
                mError = true;

                if (rp != null)
                {
                    rp.TieneDatos = false;
                    rp.AddError(ex.Message);
                }
            }
        }

        public void ExecuteDataTable(ref DotNetResponse.SQL.Persistence rp, string sentencia)
        {
            try
            {
                if (rp == null)
                    throw new ArgumentNullException("rp");

                if (string.IsNullOrEmpty(sentencia))
                    throw new ArgumentNullException("sentencia");

                rp.DataTable = ClientHelper.ExecuteDataset(mTransaction, sentencia).Tables[0];

                if (rp != null)
                    if (rp.TieneErrores)
                        mError = true;
            }
            catch (Exception ex)
            {
                mError = true;

                if (rp != null)
                {
                    rp.TieneDatos = false;
                    rp.AddError(ex.Message);
                }
            }
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="rp"></param>
        public void ExecuteDataTable(ref DotNetResponse.SQL.Persistence rp)
        {
            try
            {
                if (rp == null)
                    throw new ArgumentNullException("rp");

                rp.DataTable = ClientHelper.ExecuteDataset(mTransaction, rp.StoredProcedure, rp.Parametro).Tables[0];

                if (rp != null)
                    if (rp.TieneErrores)
                        mError = true;

            }
            catch (Exception ex)
            {
                mError = true;

                if (rp != null)
                {
                    rp.TieneDatos = false;
                    
                    rp.AddError(ex.Message);
                }
            }
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="rp"></param>
        /// <param name="sentencia"></param>
        public void ExecuteDataset(ref DotNetResponse.SQL.Persistence rp, string sentencia)
        {
            try
            {
                if (rp == null)
                    throw new ArgumentNullException("rp");

                if (string.IsNullOrEmpty(sentencia))
                    throw new ArgumentNullException("sentencia");

                rp.DataSet = ClientHelper.ExecuteDataset(mTransaction, sentencia);

                if (rp != null)
                    if (rp.TieneErrores)
                        mError = true;
            }
            catch (Exception ex)
            {
                mError = true;

                if (rp != null)
                {
                    rp.TieneDatos = false;
                    rp.AddError(ex.Message);
                }
            }
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="rp"></param>
        public void ExecuteDataset(ref DotNetResponse.SQL.Persistence rp)
        {
            try
            {
                if (rp == null)
                    throw new ArgumentNullException("rp");

                rp.DataSet = ClientHelper.ExecuteDataset(mTransaction, rp.StoredProcedure, rp.Parametro);

                if (rp != null)
                    if (rp.TieneErrores)
                        mError = true;
            } catch (Exception ex) {
                mError = true;

                if (rp != null)
                {
                    rp.TieneDatos = false;
                    rp.AddError(ex.Message);
                }
            }
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="rp"></param>
        /// <param name="sentencia"></param>
        public void ExecuteReader(ref DotNetResponse.SQL.Persistence rp, string sentencia)
        {
            try
            {
                if (rp == null)
                    throw new ArgumentNullException("rp");

                if (string.IsNullOrEmpty(sentencia))
                    throw new ArgumentNullException("sentencia");

                rp.GetDataReader = ClientHelper.ExecuteReader(mTransaction, sentencia);

                if (rp != null)
                    rp.TieneDatos = true;
                if (rp.TieneErrores)
                    mError = true;
            }
            catch (Exception ex)
            {
                mError = true;

                if (rp != null)
                {
                    rp.TieneDatos = false;
                    rp.AddError(ex.Message);
                }
            }
        }

        public void ExecuteReader(ref DotNetResponse.SQL.Persistence rp)
        {
            try
            {
                if (rp == null)
                    throw new ArgumentNullException("rp");

                rp.GetDataReader = ClientHelper.ExecuteReader(mTransaction, rp.StoredProcedure, rp.Parametro);

                if (rp != null)
                    rp.TieneDatos = true;
                if (rp.TieneErrores)
                    mError = true;
            }
            catch (Exception ex)
            {
                mError = true;

                if (rp != null)
                {
                    rp.TieneDatos = false;
                    rp.AddError(ex.Message);
                }
            }
        }

        /// <summary>
        /// 
        /// </summary>
        public void Rollback()
        {
            mTransaction.Rollback();
            Close();
        }

        ///// <summary>
        ///// 
        ///// </summary>
        //private void Commited()
        //{
        //    if (mTransaction == null) return;

        //    if (mError)
        //    {
        //        mTransaction.Rollback();
        //        Close();
        //    }
        //    mTransaction.Commit();
        //}

        /// <summary>
        /// 
        /// </summary>
        public void Commit()
        {
            if (mError)
            {
                mTransaction.Rollback();
                Close();

                return;
            }

            if (mConnection.State != ConnectionState.Open) {
                return;
            }

            if (mCommited)
                mTransaction.Commit();
        }

        public void Close()
        {
            mConnection.Close();
        }

        #region IDisposable Support
        // To detect redundant calls
        private bool disposedValue;

        protected virtual void Dispose(bool disposing)
        {
            if (!disposedValue) {
                if (disposing)
                    return; //TODO:free unmanaged resources when explicitly called

                //TODO: free shared unmanaged resources
            }

            disposedValue = true;
        }

        // This code added by Visual Basic to correctly implement the disposable pattern.
        public void Dispose()
        {
            // Do not change this code.  Put cleanup code in Dispose(ByVal disposing As Boolean) above.
            Close();
            Dispose(true);
            GC.SuppressFinalize(this);
            GC.Collect();
        }
        #endregion

    }
}
